package com.mmy.dao.hotel;

import java.util.List;
import java.util.Map;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.mmy.domain.hotel.VHotelPlan;

public interface VHotelPlanRepository extends JpaRepository<VHotelPlan, Integer> {
	/* 查找该计划编号的计划 */
	@Query(value = "select *  from vHotelPlan where iPlanID = ?1", nativeQuery = true)
	VHotelPlan findByPlanID(int iPlanID);

	/* 查询在这些房间编码里的计划 */
	@Transactional
	@Modifying
	@Query(value = "delete from vHotelPlan where iRoomID in (:iRoomIDs)", nativeQuery = true)
	List<VHotelPlan> findByRoomIDs(@Param("iRoomIDs") List<Integer> lstRoomID);

	/*
	 * 根据id查找
	 */
	@Query(value = "select *  from vHotelPlan where iRoomID = :iRoomID", nativeQuery = true)
	List<VHotelPlan> findByRoomID(@Param("iRoomID") int iRoomID);

	/* 查找所有计划中的最小价格的计划 */
	// @Query(value = "SELECT * FROM(SELECT MIN(fPrice),iPlanID FROM vHotelPlan
	// GROUP BY iRoomID) as vPlan ,vHotelPlan where
	// vPlan.iPlanID=vHotelPlan.iPlanID", nativeQuery = true)
	@Query(value = "SELECT * FROM vHotelPlan ", nativeQuery = true)

	List<VHotelPlan> findAllMin();

	/* 查询房间的当前日期的计划 */
	@Query(value = "SELECT * FROM vHotelPlan where Now() between dStartDate and dEndDate ", nativeQuery = true)
	List<VHotelPlan> findAllNow();

	/**
	 * 前台景区菜单首页显示查询计划日期大于当前日期的景区ID
	 * 
	 * @author 创建人 hanxl
	 * @param strScenicPrice2
	 * @param strScenicPrice1
	 * @date 创建日期 2021.03.02
	 */
	@Query(value = "select distinct iHotelID from  vHotelPlan where now() between dStartDate and dEndDate and (:strHotelPrice1 = \"\" or :strHotelPrice1<=fPrice) and (:strHotelPrice2 = \"\" or :strHotelPrice2>=fPrice) ", nativeQuery = true)
	List<Integer> getHotelID(@Param("strHotelPrice1") String strHotelPrice1,
			@Param("strHotelPrice2") String strHotelPrice2);

	/**
	 * 前台景区菜单首页显示查询这些景区ID的计划日期大于当前日期的计划最小价格
	 * 
	 * @author 创建人 hanxl
	 * @date 创建日期 2021.01.13
	 */
	@Query(value = "SELECT a.* FROM vHotelPlan a INNER JOIN ( SELECT iHotelID, MIN( fPrice ) AS fMinPrice FROM vHotelPlan WHERE NOW( ) BETWEEN dStartDate AND dEndDate GROUP BY iHotelID ) b ON a.iHotelID = b.iHotelID AND a.fPrice = b.fMinPrice ", nativeQuery = true)
	List<VHotelPlan> getMinPrice();

	/**
	 * 查询该酒店时间在计划日期内的计划
	 * 
	 * @author 修改人 hanxl
	 * @date 创建日期2021.03.02
	 */
	@Query(value = "select * from vHotelPlan where iRoomID = :iRoomID and dEndDate >= now()", nativeQuery = true)
	List<VHotelPlan> findPlanValid(@Param("iRoomID") int iRoomID);

	/**
	 * 查询该酒店时间在计划日期内的计划
	 * 
	 * @author 修改人 hanxl
	 * @date 创建日期2021.03.02
	 */
	@Query(value = "select DISTINCT iRoomID,cRoomName from vHotelPlan where iHotelID = :iHotelID and dEndDate >= now() ", nativeQuery = true)
	List<Map<String, Object>> findPlanValidRoom(@Param("iHotelID") int iHotelID);

	/**
	 * 函数功能说明：通过线路ID和时间查询：当天的计划单
	 *
	 * @param 参数说明
	 * @return 返回值说明 list
	 * @throws 异常处理说明
	 * @author 创建人liys
	 * @date 创建日期 2021.3.02
	 */
	@Query(value = "select iPlanID,cService from vHotelPlan where iRoomID = :iRoomID and dEndDate >= :nowDate", nativeQuery = true)
	List<Map<String, Object>> findPlanValidMap(@Param("iRoomID") int iRoomID, @Param("nowDate") String strNowDate);

	/**
	 * 函数功能说明： 进入后台酒店信息页面查询该酒店房间计划详情信息
	 *
	 * @return 返回值说明 jsp页面
	 * @throws 异常处理说明
	 * @author 创建人 hanxl
	 * @date 创建日期 2020.12.24
	 */
	@Query(value = "select * from vHotelPlan where iHotelID=?1", nativeQuery = true)
	List<VHotelPlan> findByHotelID(int iHotelID);

}
